package pt.fct.mpc.rfbul.database;

import java.io.File;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import pt.fct.mpc.rfbul.misc.PositionDevice;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.os.Environment;
import android.util.Log;


public class MapperDatabaseAdapter
{
	private static final String TAG = "RFBUL Database Adapter";
	protected static final String MAPPER_DB_NAME = "rfbul";
	private static String MAPPER_DB_PATH;
	protected static final int MAPPER_DB_VERSION = 2;
	
	
	private static MapperDatabaseAdapter instance = null;
//	private static DatabaseHelper db_helper;
	protected static SQLiteDatabase db_conn = null;

	
	public static final String MAPPER_TABLE_MAP = "map";
	public static final String MAPPER_TABLE_MAP_COL_BUILDING = "building";
	public static final String MAPPER_TABLE_MAP_COL_FLOOR = "floor";
	public static final String MAPPER_TABLE_MAP_COL_WING = "wing";
	public static final String MAPPER_TABLE_MAP_COL_ROOM = "room";
	public static final String MAPPER_TABLE_MAP_COL_MAC = "mac";
	public static final String MAPPER_TABLE_MAP_COL_SIGNAL = "signal";
	public static final String MAPPER_TABLE_MAP_COL_REL_POS = "relative_pos";
	public static final String MAPPER_TABLE_MAP_COL_REF_POS = "reference_pos";
	public static final String MAPPER_TABLE_MAP_COL_SSID = "ssid";
	
	
	
	protected static final String MAPPER_DB_CREATE_TABLE_MAP =
		"create table "+MAPPER_TABLE_MAP+" ("
		+"_id integer primary key autoincrement,"
		+MAPPER_TABLE_MAP_COL_BUILDING+" text non null,"
		+MAPPER_TABLE_MAP_COL_FLOOR+" text non null,"
		+MAPPER_TABLE_MAP_COL_WING+" text non null,"
		+MAPPER_TABLE_MAP_COL_ROOM+" text non null,"
		+MAPPER_TABLE_MAP_COL_SSID+" text non null,"
		+MAPPER_TABLE_MAP_COL_MAC+" text non null,"
		+MAPPER_TABLE_MAP_COL_SIGNAL+" integer non null,"
		+MAPPER_TABLE_MAP_COL_REL_POS+" text non null,"
		+MAPPER_TABLE_MAP_COL_REF_POS+" text non null"
		+");";
	
	protected static final String MAPPER_DB_DROP_TABLE_MAP =
		"drop table if exists "+MAPPER_TABLE_MAP;
	
	
	public static final String MAPPER_TABLE_ERROR = "error";
	public static final String MAPPER_TABLE_ERROR_COL_SEL_ROOM = "sel_room";
	public static final String MAPPER_TABLE_ERROR_COL_SEL_RELPOS = "sel_rel_pos";
	public static final String MAPPER_TABLE_ERROR_COL_SEL_ABSPOS = "sel_abs_pos";
	public static final String MAPPER_TABLE_ERROR_COL_FOUND_ROOM = "found_room";
	public static final String MAPPER_TABLE_ERROR_COL_FOUND_RELPOS = "found_rel_pos";
	public static final String MAPPER_TABLE_ERROR_COL_FOUND_ABSPOS = "found_abs_pos";
	public static final String MAPPER_TABLE_ERROR_COL_MATCH = "match";
	
	protected static final String MAPPER_DB_CREATE_TABLE_ERROR =
		"create table"+MAPPER_TABLE_ERROR+" ("
		+"_id integer primary key autoincrement,"
		+MAPPER_TABLE_ERROR_COL_SEL_ROOM+" text non null,"
		+MAPPER_TABLE_ERROR_COL_SEL_RELPOS+" text non null,"
		+MAPPER_TABLE_ERROR_COL_SEL_ABSPOS+" text non null,"
		+MAPPER_TABLE_ERROR_COL_FOUND_ROOM+" text non null,"
		+MAPPER_TABLE_ERROR_COL_FOUND_RELPOS+" text non null,"
		+MAPPER_TABLE_ERROR_COL_FOUND_ABSPOS+" text non null,"
		+MAPPER_TABLE_ERROR_COL_MATCH+" boolean"
		+");";
	
	protected static final String MAPPER_DB_DROP_TABLE_ERROR =
		"drop table if exists "+MAPPER_TABLE_ERROR;
	
	private Context context;
	
	
	protected MapperDatabaseAdapter(Context context)
	{
		this.context = context;
		
		File external_path_file = Environment.getExternalStorageDirectory();
		if (external_path_file == null)
			MAPPER_DB_PATH = MAPPER_DB_NAME;
		else
		{
			String external_path = external_path_file.getAbsolutePath();
			String app_name = context.getPackageName();
			
			String mapper_db_dir_path = external_path + "/data/" + app_name;
			MAPPER_DB_PATH = mapper_db_dir_path + "/" + MAPPER_DB_NAME+".db";
			
			File db_path_file = new File(mapper_db_dir_path);
			Log.d(TAG, "DB File path = "+db_path_file.getAbsolutePath());

			if (db_path_file.exists())
				Log.d(TAG, "DB File path exists!");
			else
			{
				boolean ret = db_path_file.mkdirs();
				Log.d(TAG, "DB File path successfuly created? "+ret);
			}
		}
	}
	
	static public MapperDatabaseAdapter getInstance(Context context)
	{
		MapperDatabaseAdapter db_adapter = new MapperDatabaseAdapter(context);
		if (instance == null)
			instance = db_adapter;
		
		return db_adapter;
	}
	
	
	public SQLiteDatabase open()
	{
		checkDatabase();
		
		Log.d(TAG, "Opening Database");
		db_conn = SQLiteDatabase.openDatabase(MAPPER_DB_PATH, null, 
			SQLiteDatabase.OPEN_READWRITE);
		Log.d(TAG, "Database = "+db_conn);
		
		return db_conn;
	}
	
	private void checkDatabase()
	{
		SQLiteDatabase db = null;
		try
		{
			db = SQLiteDatabase.openDatabase(MAPPER_DB_PATH, null,
				SQLiteDatabase.OPEN_READWRITE);
		} catch (SQLiteException e)
		{
			// Database, most likely, does not exists.
			Log.d(TAG, "Creating database at path "+MAPPER_DB_PATH);
			createDatabase();
		} finally
		{
			if (db != null)
				db.close();
			
			checkUpgrade();
		}
	}

	private void createDatabase()
	{
		SQLiteDatabase db = 
			SQLiteDatabase.openOrCreateDatabase(MAPPER_DB_PATH, null);
		db.execSQL(MAPPER_DB_CREATE_TABLE_MAP);
		db.execSQL(MAPPER_DB_CREATE_TABLE_ERROR);
		db.setVersion(MAPPER_DB_VERSION);
		db.close();
	}

	private void checkUpgrade()
	{
		SQLiteDatabase db = SQLiteDatabase.openDatabase(MAPPER_DB_PATH, 
			null, SQLiteDatabase.OPEN_READWRITE);
		
		boolean needs_upgrade = db.needUpgrade(MAPPER_DB_VERSION);
		if (needs_upgrade == false)
		{
			db.close();
			return;
		}
		
		db.execSQL(MAPPER_DB_DROP_TABLE_MAP);
		db.execSQL(MAPPER_DB_CREATE_TABLE_MAP);
		
		db.execSQL(MAPPER_DB_DROP_TABLE_ERROR);
		db.execSQL(MAPPER_DB_CREATE_TABLE_ERROR);
		db.setVersion(MAPPER_DB_VERSION);
		
		db.close();
	}

	protected void close()
	{
		db_conn.close();
		db_conn = null;
//		db_helper.close();
	}
	
	public void addEntry(String building, String floor, String wing, String room,
		String ssid, String mac, int signal, 
		String rel_position, int x, int y, int z)
	{
		SQLiteDatabase conn = this.open();

		//		String[] cols = new String[] { 
		//			LASTFM_TABLE_FAVOURITES_NAME, 
		//			LASTFM_TABLE_FAVOURITES_TYPE,
		//		};
		//		String select = LASTFM_TABLE_FAVOURITES_NAME+"='"+favourite_name
		//		+"' and "+LASTFM_TABLE_FAVOURITES_TYPE+"='"+favourite_type+"'";

//		conn.beginTransaction();
		
		if (room == null)
			room = "";
		
		ContentValues values = new ContentValues();
		values.put(MAPPER_TABLE_MAP_COL_BUILDING, building);
		values.put(MAPPER_TABLE_MAP_COL_FLOOR, floor);
		values.put(MAPPER_TABLE_MAP_COL_WING, wing);
		values.put(MAPPER_TABLE_MAP_COL_ROOM, room);
		values.put(MAPPER_TABLE_MAP_COL_SSID, ssid);
		values.put(MAPPER_TABLE_MAP_COL_MAC, mac);
		values.put(MAPPER_TABLE_MAP_COL_SIGNAL, signal);
		values.put(MAPPER_TABLE_MAP_COL_REL_POS, rel_position);
		
		String ref_position = x + ":" + y + ":" + z;
		values.put(MAPPER_TABLE_MAP_COL_REF_POS, ref_position);
		
		conn.insert(MAPPER_TABLE_MAP, null, values);
		
		conn.close();
	}
	
	public void addEntry(String building, String floor, String wing, String room,
		String ssid, String mac, int signal, String rel_position)
	{
		this.addEntry(building, floor, wing, room, ssid, mac, signal, 
			rel_position, -1, -1, -1);
	}

	public Map<String, List<MapperDatabaseEntry>> 
	getEntriesByMAC(Set<PositionDevice> devices, String exclude_criterion)
	{
		if (devices.size() == 0)
			return null;
		
		String[] query_columns = {
			MAPPER_TABLE_MAP_COL_BUILDING, MAPPER_TABLE_MAP_COL_FLOOR,
			MAPPER_TABLE_MAP_COL_MAC, MAPPER_TABLE_MAP_COL_REF_POS,
			MAPPER_TABLE_MAP_COL_REL_POS, MAPPER_TABLE_MAP_COL_ROOM,
			MAPPER_TABLE_MAP_COL_SIGNAL, MAPPER_TABLE_MAP_COL_WING
		};
		
//		String[] mac_array = new String[macSet.size()];
//		mac_array = (String[]) macSet.toArray(mac_array);
//
//		String selection_str = "";
//		int total_macs = mac_array.length;
//		for (int i = 0; i < total_macs; )
//		{
//			selection_str += MAPPER_TABLE_MAP_COL_MAC+"='"+mac_array[i]+"'";
//			if ((++ i) < total_macs)
//				selection_str += " OR ";
//		}
//		
//		Log.d(TAG, "query_str = "+selection_str);
		
		SQLiteDatabase conn = this.open();
		Cursor cursor;
//		cursor = conn.query(MAPPER_TABLE_MAP, 
//			query_columns, selection_str, null, null, null, null);
		
		String groupby_str = MAPPER_TABLE_MAP_COL_ROOM
			+","+MAPPER_TABLE_MAP_COL_REL_POS
			+","+MAPPER_TABLE_MAP_COL_MAC;
		
//		select map1.room as r,map1.mac as mac1,map1.relative_pos as position,
//		map2.mac as mac2, map3.mac as mac3 from map map1,map map2, map map3 
//		where (r=map2.room and r=map3.room and position=map2.relative_pos and 
//		position=map3.relative_pos and mac1='00:11:21:6c:4a:b0' 
//		and mac2='00:12:da:9e:31:50' and mac3='00:12:da:9e:2f:d0');
		
//		cursor = conn.query(MAPPER_TABLE_MAP, 
//			query_columns, selection_str, null, groupby_str, having, orderBy)
		
//		int col_building_index = 
//			cursor.getColumnIndexOrThrow(MAPPER_TABLE_MAP_COL_BUILDING);
//		int col_floor_index =
//			cursor.getColumnIndexOrThrow(MAPPER_TABLE_MAP_COL_FLOOR);
//		int col_mac_index = 
//			cursor.getColumnIndexOrThrow(MAPPER_TABLE_MAP_COL_MAC);
//		int col_refpos_index =
//			cursor.getColumnIndexOrThrow(MAPPER_TABLE_MAP_COL_REF_POS);
//		int col_relpos_index =
//			cursor.getColumnIndexOrThrow(MAPPER_TABLE_MAP_COL_REL_POS);
//		int col_room_index =
//			cursor.getColumnIndexOrThrow(MAPPER_TABLE_MAP_COL_ROOM);
//		int col_signal_index =
//			cursor.getColumnIndexOrThrow(MAPPER_TABLE_MAP_COL_SIGNAL);
//		int col_wing_index =
//			cursor.getColumnIndexOrThrow(MAPPER_TABLE_MAP_COL_WING);

		int total_macs = devices.size();
		PositionDevice[] mac_array = new PositionDevice[total_macs];
		mac_array = (PositionDevice[]) devices.toArray(mac_array);
		
		
		String QUERY_COL_ROOM = "r";
		String QUERY_COL_REL_POSITION = "position";
		
		String macs_query_where = "";
		String macs_query_projection = "";
		String map_query_from = ""; // for the "map map{i}" stuff.
		String map_query_where = "";
		String signal_query_projection = "";
		for (int i = 1; i <= total_macs;)
		{
			macs_query_where += "mac"+i+"='"+mac_array[i-1].BSSID+"'";
			macs_query_projection += "map"+i+".mac as mac"+i;
			signal_query_projection += "map"+i+".signal as signal"+i;
			map_query_from += MAPPER_TABLE_MAP+" map"+i;
			
			if (i > 1)
				map_query_where += 
					QUERY_COL_ROOM+"=map"+i+".room"
					+" and "+QUERY_COL_REL_POSITION+"=map"+i+".relative_pos";
			
			if ((++ i) <= total_macs)
			{
				macs_query_where += " and ";
				macs_query_projection += ", ";
				signal_query_projection += ", ";
				map_query_from += ", ";
				
				if ((i-1) > 1)
					map_query_where += " and ";
			}
		}
		
		String query_select = 
			"map1.room as "+QUERY_COL_ROOM
			+", map1.relative_pos as "+QUERY_COL_REL_POSITION
			+", "+macs_query_projection
			+", "+signal_query_projection;
		String query_where =
			map_query_where + " and " + macs_query_where;
		
		if (exclude_criterion != null)
			query_where += "and "+exclude_criterion;
		
		String query_str = 
			"select "
			+query_select
			+" from "
			+map_query_from
			+" where (" + query_where + ");";
		
		Log.d(TAG, "== QUERY STRING ==");
		Log.d(TAG, "select "+query_select+"\n"
			+" from "+map_query_from+"\n"
			+"where ("+query_where + ");");
		
//		String raw_query_str =
//			"select map1.room as r,map1.mac as mac1,map1.relative_pos as position,"
//			+" map2.mac as mac2, map3.mac as mac3 from map map1,map map2, map map3" 
//			+" where (r=map2.room and r=map3.room and position=map2.relative_pos and" 
//			+" position=map3.relative_pos and mac1='00:11:21:6c:4a:b0'" 
//			+" and mac2='00:12:da:9e:31:50' and mac3='00:12:da:9e:2f:d0');";
		
		String raw_query_str = query_str;
		
		cursor = conn.rawQuery(raw_query_str, null);


		int col_room_idx = cursor.getColumnIndexOrThrow(QUERY_COL_ROOM);
		int col_pos_idx = cursor.getColumnIndexOrThrow(QUERY_COL_REL_POSITION);
		
		int[] col_mac_idx = new int[total_macs];
		int[] col_signal_idx = new int[total_macs];
		for (int i = 0; i < total_macs; i ++)
		{
			col_mac_idx[i] = cursor.getColumnIndexOrThrow("mac"+(i+1));
			col_signal_idx[i] = cursor.getColumnIndexOrThrow("signal"+(i+1));
		}
		
		cursor.moveToFirst();

		int total_entries = cursor.getCount();
		MapperDatabaseEntry[] entries = 
			new MapperDatabaseEntry[total_entries];

		Map<String, List<MapperDatabaseEntry>> entries_map = 
			new HashMap<String, List<MapperDatabaseEntry>>();
		
		for (int i = 0; i < total_entries; i ++)
		{
			String[] mac_values = new String[total_macs];
			int[] signal_values = new int[total_macs];
			
			String room = cursor.getString(col_room_idx);
			String pos = cursor.getString(col_pos_idx);
			
			String macs_str = "";
			for (int m = 0; m < total_macs; m ++)
			{
				mac_values[m] = cursor.getString(col_mac_idx[m]);
				signal_values[m] = cursor.getInt(col_signal_idx[m]);
				macs_str += mac_values[m] + " ("+signal_values[m]+") // ";
			}
			
			Log.d(TAG, "room: "+room+", pos = "+pos
				+", macs: "+macs_str);

//			entries[i] = new MapperDatabaseEntry(building, floor, wing, room,
//			mac, signal, rel_pos, ref_pos);
			
			MapperDatabaseEntry entry = 
				new MapperDatabaseEntry(null, null, null, room, 
					mac_values, signal_values, pos, null);
			
			List<MapperDatabaseEntry> known_entries = entries_map.get(room);
			if (known_entries == null)
			{
				known_entries = new LinkedList<MapperDatabaseEntry>();
				entries_map.put(room, known_entries);
			}
			known_entries.add(entry);

//			
//			entries[i] = new MapperDatabaseEntry(building, floor, wing, room,
//					mac, signal, rel_pos, ref_pos);
			
			cursor.moveToNext();
		}
		
		cursor.close();
		conn.close();
		
		return entries_map;
	}
	
	public void addErrorEntry(DatabasePosition selected, DatabasePosition found)
	{
		boolean match = (selected.room.equalsIgnoreCase(found.room));
		
		Log.d(TAG, "Adding entry into error table");

		ContentValues values = new ContentValues();
		
		values.put(MAPPER_TABLE_ERROR_COL_SEL_ROOM, selected.room);
		values.put(MAPPER_TABLE_ERROR_COL_SEL_RELPOS, selected.rel);
		values.put(MAPPER_TABLE_ERROR_COL_SEL_ABSPOS, selected.abs);
		values.put(MAPPER_TABLE_ERROR_COL_FOUND_ROOM, found.room);
		values.put(MAPPER_TABLE_ERROR_COL_FOUND_RELPOS, found.rel);
		values.put(MAPPER_TABLE_ERROR_COL_FOUND_ABSPOS, found.abs);
		values.put(MAPPER_TABLE_ERROR_COL_MATCH, match);
		
		SQLiteDatabase conn = this.open();
		conn.insert(MAPPER_TABLE_ERROR, null, values);
		conn.close();
	}
	
	public static final int ERROR_ENTRIES_MATCH = 0x0;
	public static final int ERROR_ENTRIES_NO_MATCH = 0x01;
	public static final int ERROR_ENTRIES_ALL = 0x02;
	
	public int getErrorTotalEntries(int what)
	{
		int ret = 0;
		
		String query = "select count(*) as count from "+MAPPER_TABLE_ERROR;
		
		switch (what)
		{
			case ERROR_ENTRIES_MATCH:
				query += " where "+MAPPER_TABLE_ERROR_COL_MATCH+"=1";
				break;
			case ERROR_ENTRIES_NO_MATCH:
				query += " where "+MAPPER_TABLE_ERROR_COL_MATCH+"=0";
				break;
		}
		
		SQLiteDatabase conn = this.open();

		Cursor cursor = conn.rawQuery(query, null);
		
		if (cursor.getCount() > 0)
		{
			cursor.moveToFirst();
			
			int col_count_idx = cursor.getColumnIndexOrThrow("count");
			
			ret = cursor.getInt(col_count_idx);
		}
		
		cursor.close();
		conn.close();
		
		return ret;
	}
	
	public List<DatabasePosition[]> getErrorEntries(int what)
	{
		List<DatabasePosition[]> entries = 
			new LinkedList<DatabasePosition[]>();
			
		String query = "select * from "+MAPPER_TABLE_ERROR;
		switch (what)
		{
			case ERROR_ENTRIES_MATCH:
				query += " where "+MAPPER_TABLE_ERROR_COL_MATCH+"=1";
				break;
			case ERROR_ENTRIES_NO_MATCH:
				query += " where "+MAPPER_TABLE_ERROR_COL_MATCH+"=0";
				break;
		}
		
		SQLiteDatabase conn = this.open();
		Cursor cursor = conn.rawQuery(query, null);
		
		if (cursor.getCount() > 0)
		{
			cursor.moveToFirst();
			
			int col_idx_sel_room = 
				cursor.getColumnIndexOrThrow(MAPPER_TABLE_ERROR_COL_SEL_ROOM);
			int col_idx_sel_relpos =
				cursor.getColumnIndexOrThrow(MAPPER_TABLE_ERROR_COL_SEL_RELPOS);
			int col_idx_sel_abspos =
				cursor.getColumnIndexOrThrow(MAPPER_TABLE_ERROR_COL_SEL_ABSPOS);
			int col_idx_fnd_room =
				cursor.getColumnIndexOrThrow(MAPPER_TABLE_ERROR_COL_FOUND_ROOM);
			int col_idx_fnd_relpos =
				cursor.getColumnIndexOrThrow(MAPPER_TABLE_ERROR_COL_FOUND_RELPOS);
			int col_idx_fnd_abspos =
				cursor.getColumnIndexOrThrow(MAPPER_TABLE_ERROR_COL_FOUND_ABSPOS);

			for (int i = 0; i < cursor.getCount(); i ++)
			{
				String sel_room = cursor.getString(col_idx_sel_room);
				String sel_relpos = cursor.getString(col_idx_sel_relpos);
				String sel_abspos = cursor.getString(col_idx_sel_abspos);
				
				String fnd_room = cursor.getString(col_idx_fnd_room);
				String fnd_relpos = cursor.getString(col_idx_fnd_relpos);
				String fnd_abspos = cursor.getString(col_idx_fnd_abspos);
				
				DatabasePosition[] pos = new DatabasePosition[2];
				
				pos[0] =
					new DatabasePosition(sel_room, sel_relpos, sel_abspos);
				pos[1] =
					new DatabasePosition(fnd_room, fnd_relpos, fnd_abspos);
				
				entries.add(pos);
				
				cursor.moveToNext();
			}
			
		}
		
		cursor.close();
		conn.close();
	
		return entries;
	}
	
	static public DatabasePosition 
	createPosition(String room, String rel, String abs)
	{
		return new MapperDatabaseAdapter.DatabasePosition(room, rel, abs);
	}
	
	public static class DatabasePosition
	{
		String room;
		String rel;
		public String abs;
		
		public DatabasePosition(String room, String rel, String abs)
		{
			this.room = room;
			this.rel = rel;
			this.abs = abs;
		}
	}
}
